package com.qn.utils;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.exception.ExcelDataConvertException;
import com.alibaba.excel.read.listener.ReadListener;
import com.qn.dao.CompanydataDao;
import com.qn.entity.CompanyDataVo;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Proxy;
import java.util.*;

/**
 * <h1>表格导入工具类</h1>
 *
 * @author LinXiPeng
 * @className ExcelImportTool
 * @date 2021/11/4 22:04
 * Version 1.0
 */
@Slf4j
@Component
public class ExcelImportTool {
    @Resource
    CompanydataDao companydataDao;

    /**
     * 导入表格数据
     *
     * @return void
     * @description TODO
     * @author LinXiPeng
     * @date 2021/11/5
     */
    public void importExcel(MultipartFile file) throws IOException {
        //前置操作 这个class的注解必须提前匹配并设置好注解,否则没有匹配上的将无法读取到数据
        EasyExcel.read(deleteFirstRow(file), CompanyDataVo.class, new ReadListener<CompanyDataVo>() {
            /**
             * 单次缓存的数据量
             */
            private final Integer BATCH_COUNT = 3000;
            /**
             *临时存储,初始大小即单次缓存的数据量
             */
            private List<CompanyDataVo> cachedData = new ArrayList<>(BATCH_COUNT);

            /**
             * 读取行
             */
            @Override
            public void invoke(CompanyDataVo companyDataVo, AnalysisContext analysisContext) {
                //每读一行就添加到临时数组里面
                cachedData.add(companyDataVo);
                //如果数组的长度达到了单次缓存的数据量,则进行数据的存储,防止一次性插入大量数据导致系统崩溃
                if (cachedData.size() >= BATCH_COUNT) {
                    companydataDao.insert(cachedData);
                    // 存储完成清理 list
                    cachedData = new ArrayList<>(BATCH_COUNT);
                }
            }

            /**
             * 最后一次数据导入
             *
             * @param analysisContext 参数说明
             */
            @Override
            public void doAfterAllAnalysed(AnalysisContext analysisContext) {
                companydataDao.insert(cachedData);
            }

            /**
             * 读取表格时出现异常的处理
             *
             * @param exception 异常
             * @param context 正文
             */
            @Override
            public void onException(Exception exception, AnalysisContext context) {
                log.error("解析失败，但是继续解析下一行:{}", exception.getMessage());
                if (exception instanceof ExcelDataConvertException) {
                    ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException) exception;
                    log.error("第{}行，第{}列解析异常", excelDataConvertException.getRowIndex(),
                            excelDataConvertException.getColumnIndex());
                }
            }
        }).sheet().doRead();
    }

    /**
     * 删除表格首行
     *
     * @return void
     * @description TODO
     * @author LinXiPeng
     * @date 2021/11/5
     */
    public File deleteFirstRow(MultipartFile file) throws IOException {
        //导入前先删除首行,否则标题将对应不上
        InputStream fileInputStream = file.getInputStream();
        HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream);
        HSSFSheet sheet = workbook.getSheetAt(0);
        //删除首行的合并样式,否则下一行向上平移时会自动继承该行的合并,导致部分标题也被合并,其他的列就无法根据标题读取数据的情况
        sheet.removeMergedRegion(1);
        //行的平移,第一行到最后一行向上平移1格
        sheet.shiftRows(1, sheet.getLastRowNum(), -1);
        //建立临时文件
        File tempFile = File.createTempFile("temp", "excel");
        FileOutputStream os = new FileOutputStream(tempFile);
        workbook.write(os);
        fileInputStream.close();
        os.close();
        FileInputStream tempFileInput = new FileInputStream(tempFile);
        //根据首行数据匹配反射修改注解匹配参数
        HSSFWorkbook tempExcelWorkbook = new HSSFWorkbook(tempFileInput);
        Iterator<Cell> cellIterator = tempExcelWorkbook.getSheetAt(0).getRow(0).cellIterator();
        cellIterator.forEachRemaining(ele -> {
            try {
                //类的注解反射设置
                this.dynamicAnnotation(ele.getStringCellValue());
            } catch (NoSuchFieldException | IllegalAccessException e) {
                log.error(e.getMessage());
            }
        });
        tempFileInput.close();
        return tempFile;
    }

    /**
     * 动态匹配标题并设置变种类的注解值
     *
     * @param title 需要匹配的标题
     * @return void
     * @description TODO
     * @author LinXiPeng
     * @date 2021/11/5
     */
    @SuppressWarnings("unchecked")
    private void dynamicAnnotation(String title) throws NoSuchFieldException, IllegalAccessException {
        //空标题直接退出
        if ("".equals(title.trim())) {
            return;
        }
        //与变种类的字段进行匹配
        Field[] declaredFields = CompanyDataVo.class.getDeclaredFields();
        for (Field declaredField : declaredFields) {
            ExcelProperty excelProperty = declaredField.getDeclaredAnnotation(ExcelProperty.class);
            if (excelProperty == null) {
                continue;
            }
            //如果相等就直接退出循环,不需要匹配了
            if (excelProperty.value()[0].equals(title)) {
                break;
            }
            //匹配字符数量
            int matchNum = 0;
            char[] titleCharArr = title.toCharArray();
            char[] annotationVal = excelProperty.value()[0].toCharArray();
            //字符比对
            for (char tiVal : titleCharArr) {
                for (char an : annotationVal) {
                    if (tiVal == an) {
                        matchNum++;
                    }
                }
            }
            //如果匹配字符大于8才修改注解值
            if (matchNum > 8) {
                //如果不是全部不匹配,并且有几个字匹配上了,就直接进行赋值
                log.info("[表格标题:{}]与[变种类注解值:{}]匹配成功,正在修改值", title, excelProperty.value()[0]);
                //获得代理对象
                InvocationHandler invocationHandler = Proxy.getInvocationHandler(excelProperty);
                //获取字段
                Field memberValues = invocationHandler.getClass().getDeclaredField("memberValues");
                //开启暴力破解权限
                memberValues.setAccessible(true);
                Map<String, Object> map = (Map<String, Object>) memberValues.get(invocationHandler);
                map.put("value", new String[]{title});
                log.info("修改后的值为" + excelProperty.value()[0]);
                break;
            }
        }
    }
}